from django.core.paginator import Paginator
from django.db import IntegrityError, transaction
from django.db.models import Q
from django.http import HttpResponse
from django.shortcuts import render

from clazz.models import Clazz
from course.models import Course
from student.models import Student
from utils import responsedata
from utils.customerutils import CustomerUtils
from utils.responsestatus import ResponseStatus
import pandas as pd
from openpyxl.workbook import Workbook
# from openpyxl import Workbook


# Create your views here.


def student_page(request):
    """
    学生信息分页 搜索 班级名 学生名
    1. 跟据班级名称查询班级id
    2. 学生名 直接作为条件使用
    :param request:
    :return:
    """
    if request.method == 'POST':
        # {'page': 1, 'size': 5}
        form_data = CustomerUtils.get_request_data(request)

        # 空条件
        condition = Q()
        # 动态条件 存在就拼接
        # 如果传了班级名
        if form_data.get('class_name').strip():
            # 跟据班级名找数据
            clazz = Clazz.objects.filter(class_name=form_data.get('class_name')).first()
            print(clazz)
            if clazz:
                condition &= Q(clazz_id=clazz.id)
            else:
                condition &= Q(clazz_id=None)
        if form_data.get('student_name').strip():
            condition &= Q(student_name__icontains=form_data['student_name'])

        # 分页处理 传回去total条数 tableData数据
        student_list = Student.objects.filter(condition).order_by('-id')
        # 获取分页对象
        paginator = Paginator(student_list, form_data.get('size', 5))
        # 获取数据总条数
        total = paginator.count
        # 当前页对象
        current_page = paginator.page(form_data.get('page', 1))
        # 当前页数据 [{},{},{}]
        table_data = current_page.object_list

        # 查询user有关联表，需要特殊处理
        students = []
        for student in table_data:
            students.append({
                'id': student.id,
                'student_name': student.student_name,
                'student_no': student.student_no,
                'student_age': student.student_age,
                'student_sex': '男' if student.student_sex == 1 else '女',
                'student_tel': student.student_tel,
                'student_state': student.student_state,
                'school_time': student.school_time.strftime('%Y-%m-%d'),
                'class_name': student.clazz.class_name,
                'course_name': student.course.course_name
            })
        response = {
            'code': ResponseStatus.BUSINESS_SUCCESS,
            'data': {
                'total': total,
                'tableData': students
            }
        }

        return CustomerUtils.response_json(response)


def student_one(request, id):
    student = Student.objects.filter(id=id).values().first()
    response = {
        'code': ResponseStatus.BUSINESS_SUCCESS,
        'data': {
            'student': student
        }
    }
    return CustomerUtils.response_json(response)


def student_insert_edit(request):
    if request.method == 'POST':
        form_data = CustomerUtils.get_request_data(request)
        student_id = form_data.get('id')
        try:
            if student_id is not None:
                # 更新操作
                Student.objects.filter(id=student_id).update(**form_data)
            else:
                Student(**form_data).save()
        except IntegrityError as e:
            response = responsedata.BUSINESS_ERROR_DATA
            response['message'] = str(e)
            return CustomerUtils.response_json(response)
        else:
            return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)


def student_remove(request, id):
    Student.objects.filter(id = id).delete()
    return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)


def student_change_state(request):
    if request.method == 'POST':
        form_data = CustomerUtils.get_request_data(request)
        Student.objects.filter(id=form_data.get('id')).update(student_state=form_data.get('state'))
        return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)

# 当前视图中多条语句被添加在一个事务中
@transaction.atomic
def import_excel(request):
    """
    上传excel文件 解析excel

    事务：
        执行多条sql语句时，将多条sql加入同一个事务环境下，让多条sql全部成功或全部失败（提交/回滚）
    事务的特点：
        原子性：最小单位
        一致性：执行前后同一状态
        隔离性：可以同时执行多个事务，互不影响，有级别
        持久性： 一旦提交，永久保存
    事务隔离级别：
        读未提交：A事务可以读到B事务未提交的数据
        读已提交：A事务不可以读到B事务未提交的数据
        可重复读：A事务实时读到别的事务新提交的数据（不可重复读级别下，只能读到请求那一刻的数据，新加的不刷新）
        串行化：同时只执行一个事务。最安全、但慢


    :param request:
    :return:
    """
    if request.method == 'POST':
        file = request.FILES['file']
        df = pd.read_excel(file)
        try:
            for index, row in df.iterrows():
                # 课程名、班级名查询id
                clazz_id = Clazz.objects.filter(class_name=row['班级']).first().id
                course_id = Course.objects.filter(course_name=row['课程']).first().id
                student = Student(
                    student_no = row['学号'],
                    student_name = row['姓名'],
                    student_age = row['年龄'],
                    student_sex = 1 if row['性别'] == '男' else 2,
                    student_tel = row['联系方式'],
                    clazz_id = clazz_id,
                    course_id = course_id,
                    school_time = row['入学时间'],
                    student_state = 1 if row['状态'] == '在学' else 2,
                )
                student.save()
        except AttributeError:
            response = responsedata.BUSINESS_ERROR_DATA
            response['message'] = '班级或课程名字不存在'
            return CustomerUtils.response_json(response)
        except IntegrityError as e:
            response = responsedata.BUSINESS_ERROR_DATA
            response['message'] = str(e)
            return CustomerUtils.response_json(response)
        else:
            return CustomerUtils.response_json(responsedata.BUSINESS_SUCCESS_DATA)


def export_excel(request):
    # 创建excel工作簿
    work_book = Workbook()
    # 获取工作簿的一张表
    sheet = work_book.active
    # 设置表名
    sheet.title = '学生信息表'
    # 表头
    header = ['学号','姓名','年龄','性别','联系方式','课程','班级','入学时间','状态']
    # 追加表头行
    sheet.append(header)

    # 表格数据
    condition = Q()
    # 动态条件 存在就拼接
    # 如果传了班级名
    class_name = request.GET.get('class_name')
    if class_name.strip():
        # 跟据班级名找数据
        clazz = Clazz.objects.filter(class_name=class_name).first()
        print(clazz)
        if clazz:
            condition &= Q(clazz_id=clazz.id)
        else:
            condition &= Q(clazz_id=None)
    student_name = request.GET.get('student_name')
    if student_name.strip():
        condition &= Q(student_name__icontains=student_name)

    students = Student.objects.filter(condition).order_by('-id')
    # 遍历，写入excel
    for student in students:
        # 性别 状态
        student_sex = '男' if student.student_sex == 1 else '女'
        student_state = '在学' if student.student_state == 1 else '休学'
        # 课程 班级
        course_name = Course.objects.filter(id=student.course_id).first().course_name
        class_name = Clazz.objects.filter(id=student.clazz_id).first().class_name
        row = [
            student.student_no,
            student.student_name,
            student.student_age,
            student_sex,
            student.student_tel,
            course_name,
            class_name,
            student.school_time.strftime('%Y-%m-%d'),
            student_state
        ]
        sheet.append(row)

    # 设置响应对象 文件形式
    response = HttpResponse(content_type='application/vnd.openxmlformats-officedocument.spreadsheetml.sheet')
    # 将excel以附件形式响应     attachment附件，浏览器不需要打开
    response['Content-Disposition'] = 'attachment; filename="students.xlsx"'
    # 将表格和response对象绑定
    work_book.save(response)
    return response






